﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;
using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataTransferObject.MetaDataList;
using DataAccessLayer;
using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.QuotaDTSTableAdapters;

namespace BusinessLogicLayer.MetaDataList
{
    public class QuotaBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.Quota; } }

        private UserInformation userOrganization;

        private QuotaMaster quotaMaster;

        private MyBindingList<QuotaDetail> quotaDetailList;

        private bool ReadonlyMode { get; set; }

        public QuotaBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                quotaMaster = new QuotaMaster();

                this.quotaDetailList = new MyBindingList<QuotaDetail>();



                this.arrayFilterDescription = new string[1, 2];



                GlobalDefaultValue.Apply(this);


                this.QuotaMaster.PropertyChanged += new PropertyChangedEventHandler(QuotaMaster_PropertyChanged);
                this.quotaDetailList.ListChanged += new ListChangedEventHandler(QuotaDetail_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void QuotaMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (!this.ReadonlyMode)
            {
                this.StopTracking();
                this.QuotaGetDetail();
                this.StartTracking();
            }

            this.SetDirty();
        }

        private void QuotaDetail_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private QuotaMasterListingTableAdapter listingTableAdapter;
        protected QuotaMasterListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new QuotaMasterListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private QuotaMasterTableAdapter masterTableAdapter;
        protected QuotaMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new QuotaMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        QuotaDetailTableAdapter quotaDetailTableAdapter;
        protected QuotaDetailTableAdapter QuotaDetailTableAdapter
        {
            get
            {
                if (quotaDetailTableAdapter == null) quotaDetailTableAdapter = new QuotaDetailTableAdapter();
                return quotaDetailTableAdapter;
            }
        }


        QuotaGetDetailTableAdapter quotaGetDetailTableAdapter;
        protected QuotaGetDetailTableAdapter QuotaGetDetailTableAdapter
        {
            get
            {
                if (quotaGetDetailTableAdapter == null) quotaGetDetailTableAdapter = new QuotaGetDetailTableAdapter();
                return quotaGetDetailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public QuotaDTS.QuotaMasterListingDataTable QuotaMasterList(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public QuotaMaster QuotaMaster
        {
            get { return this.quotaMaster; }
        }

        public MyBindingList<QuotaDetail> QuotaDetailList
        {
            get { return this.quotaDetailList; }
        }





        private string[,] arrayFilterDescription;

        /// <summary>
        /// This get user friendly filter description from the arrayFilterDescription, BUT the arrayFilterDescription is only updated by this.GetFilterMarketingProgramID()
        /// So: To use this FilterDescription, please ONLY TAKE IT AFTER SOME METHED HAS CALLED This.GetFilterMarketingProgramID()
        /// </summary>
        /// <returns></returns>
        public string[,] FilterDescription()
        {
            return arrayFilterDescription;
        }

        public DataTable GetQuotaListingDetail()
        {
            arrayFilterDescription[0, 0] = "Date"; arrayFilterDescription[0, 1] = GlobalVariables.GlobalOptionSetting.LowerFillterDate.ToString("dd/MM/yyyy") + " -> " + GlobalVariables.GlobalOptionSetting.UpperFillterDate.ToString("dd/MM/yyyy");


            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("@LowerFillterDate", GlobalVariables.GlobalOptionSetting.LowerFillterDate); sqlParameter[0].SqlDbType = SqlDbType.DateTime; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@UpperFillterDate", GlobalVariables.GlobalOptionSetting.UpperFillterDate); sqlParameter[1].SqlDbType = SqlDbType.DateTime; sqlParameter[1].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("QuotaGetDetail", CommandType.StoredProcedure, sqlParameter);
        }

        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int QuotaID   //Primary Key
        {
            get { return this.QuotaMaster.QuotaID; }
            private set
            {
                if (this.QuotaMaster.QuotaID != value)
                {
                    this.StopTracking();

                    this.QuotaMaster.QuotaID = value;

                    this.QuotaGetMaster();
                    this.QuotaGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        public int CustomerTypeID
        {
            get { return this.QuotaMaster.CustomerTypeID; }
            set
            {
                if (this.QuotaMaster.CustomerTypeID != value)
                {
                    this.StopTracking();

                    this.QuotaMaster.CustomerTypeID = value;

                    this.QuotaGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }


        public int CustomerCategoryID
        {
            get { return this.QuotaMaster.CustomerCategoryID; }
            set
            {
                if (this.QuotaMaster.CustomerCategoryID != value)
                {
                    this.StopTracking();

                    this.QuotaMaster.CustomerCategoryID = value;

                    this.QuotaGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }


        public int CustomerChannelID
        {
            get { return this.QuotaMaster.CustomerChannelID; }
            set
            {
                if (this.QuotaMaster.CustomerChannelID != value)
                {
                    this.StopTracking();

                    this.QuotaMaster.CustomerChannelID = value;

                    this.QuotaGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }


        #endregion <Primary Key>

        private void QuotaGetMaster()
        {
            if (this.QuotaID > 0)
            {
                QuotaDTS.QuotaMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.QuotaID);

                if (masterDataTable.Count > 0)
                {
                    this.QuotaMaster.StopTracking();

                    this.QuotaMaster.EntryDate = masterDataTable[0].EntryDate;
                    this.QuotaMaster.QuotaDate = masterDataTable[0].QuotaDate;

                    this.QuotaMaster.Reference = masterDataTable[0].Reference;

                    this.QuotaMaster.SalesmenID = masterDataTable[0].SalesmenID;
                    this.QuotaMaster.SalesmenCategoryID = masterDataTable[0].SalesmenCategoryID;
                    this.QuotaMaster.SalesmenTypeID = masterDataTable[0].SalesmenTypeID;
                    this.QuotaMaster.SalesmenAddressAreaID = masterDataTable[0].SalesmenAddressAreaID;
                    this.QuotaMaster.LocationID = masterDataTable[0].LocationID;
                    this.QuotaMaster.DepartmentID = masterDataTable[0].DepartmentID;
                    this.QuotaMaster.SectionID = masterDataTable[0].SectionID;
                    this.QuotaMaster.TeamID = masterDataTable[0].TeamID;
                    this.QuotaMaster.EmployeeID = masterDataTable[0].EmployeeID;
                    this.QuotaMaster.CustomerCategoryID = masterDataTable[0].CustomerCategoryID;
                    this.QuotaMaster.CustomerTypeID = masterDataTable[0].CustomerTypeID;
                    this.QuotaMaster.CustomerChannelID = masterDataTable[0].CustomerChannelID;

                    this.QuotaMaster.TotalQuantityQuota = masterDataTable[0].TotalQuantityQuota;
                    this.QuotaMaster.TotalAmountQuota = masterDataTable[0].TotalAmountQuota;

                    this.QuotaMaster.Description = masterDataTable[0].Description;
                    this.QuotaMaster.Other = masterDataTable[0].Other;

                    this.QuotaMaster.InActive = masterDataTable[0].InActive;

                    this.QuotaMaster.StartTracking();

                    this.QuotaMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.QuotaMaster);
                this.QuotaMaster.QuotaDate = DateTime.Today;
                this.QuotaMaster.EntryDate = DateTime.Today;
                this.QuotaMaster.Reset();
            }
        }


        private void QuotaGetDetail()
        {

            this.quotaDetailList.RaiseListChangedEvents = false;
            this.quotaDetailList.Clear();

            if (this.CustomerCategoryID > 0 && this.CustomerChannelID > 0 && this.CustomerTypeID > 0)
            {
                QuotaDTS.QuotaGetDetailDataTable detailDataTable = this.QuotaGetDetailTableAdapter.GetData(this.QuotaID, this.CustomerCategoryID, this.CustomerTypeID, this.CustomerChannelID);

                if (detailDataTable.Count > 0)
                {
                    foreach (QuotaDTS.QuotaGetDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.quotaDetailList.Add(new QuotaDetail(detailRow.CustomerCategoryID, detailRow.CustomerTypeID, detailRow.CollectedTypeID, detailRow.CommodityID, detailRow.CustomerChannelID, detailRow.QuantityQuota12, detailRow.QuantityQuota11, detailRow.QuantityQuota10, detailRow.QuantityQuota09, detailRow.QuantityQuota08, detailRow.QuantityQuota07, detailRow.QuantityQuota06, detailRow.QuantityQuota05, detailRow.QuantityQuota04, detailRow.QuantityQuota03, detailRow.QuantityQuota02, detailRow.QuantityQuota01, detailRow.AmountQuota12, detailRow.AmountQuota11, detailRow.AmountQuota10, detailRow.AmountQuota09, detailRow.AmountQuota08, detailRow.AmountQuota07, detailRow.AmountQuota06, detailRow.AmountQuota05, detailRow.AmountQuota04, detailRow.AmountQuota03, detailRow.AmountQuota02, detailRow.AmountQuota01, detailRow.ItemCategoryName, detailRow.ItemCommodityName, detailRow.BrandName, detailRow.Unit, detailRow.IsCommodity));
                    }
                }
            }

            this.quotaDetailList.RaiseListChangedEvents = true;
            this.quotaDetailList.ResetBindings();

        }



        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<QuotaDetail> inValidQuotaDetail = this.QuotaDetailList.Where(quotaDetail => !quotaDetail.IsValid).ToList();
                return this.QuotaMaster.IsValid && inValidQuotaDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.quotaMaster.QuotaDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    //if (!GlobalUserPermission.GetEditable("SPQuotaApproved", this.QuotaID)) return false;

                    return GlobalUserPermission.GetEditable("SPQuotaEditable", this.QuotaID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.quotaMaster.QuotaDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.QuotaVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("SPQuotaEditable", this.QuotaID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.quotaMaster.QuotaDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.QuotaUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("SPQuotaEditable", this.QuotaID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int quotaID)
        {
            this.ReadonlyMode = true;
            if (this.QuotaID == quotaID) this.QuotaID = -1; //Enforce to reload
            this.QuotaID = quotaID;
        }

        public void New()
        {
            this.ReadonlyMode = false;
            if (this.QuotaID == 0) this.QuotaID = -1;
            this.QuotaID = 0;
        }

        public void Edit()
        {
            this.ReadonlyMode = false;
            int quotaID = this.QuotaID;

            this.QuotaID = -1;
            this.QuotaID = quotaID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int quotaID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref quotaID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(quotaID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }

                this.Fill(quotaID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.quotaMaster.QuotaID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.quotaMaster.QuotaID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.quotaMaster.QuotaID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    //if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {

            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.QuotaMaster.EntryDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);

            //if (exceptionTable.Table.Rows.Count <= 0) return true; else throw new CustomException("Save validate", exceptionTable.Table);

        }


        private bool SaveMaster(ref int quotaID)
        {
            QuotaDTS.QuotaMasterDataTable masterDataTable;
            QuotaDTS.QuotaMasterRow masterRow;
            QuotaDTS.QuotaListSalesmenNameDataTable quotaListSalesmenNameDataTable;
            QuotaListSalesmenNameTableAdapter quotaListSalesmenNameTableAdapter;

            if (this.quotaMaster.QuotaID <= 0) //Add
            {
                masterDataTable = new QuotaDTS.QuotaMasterDataTable();
                masterRow = masterDataTable.NewQuotaMasterRow();

                masterRow.Reference = GlobalDataObjectAction.NewIDReference("QuotaMaster", "Reference", 2);
            }
            else //Edit
            {
                if (!this.SaveUndo(quotaMaster.QuotaID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(quotaMaster.QuotaID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            quotaListSalesmenNameTableAdapter = new QuotaListSalesmenNameTableAdapter();
            quotaListSalesmenNameDataTable = quotaListSalesmenNameTableAdapter.GetData(this.quotaMaster.SalesmenID);

            masterRow.EntryDate = this.quotaMaster.EntryDate;
            masterRow.QuotaDate = this.quotaMaster.QuotaDate;

            masterRow.SalesmenID = this.quotaMaster.SalesmenID;

            if (quotaListSalesmenNameDataTable.Rows.Count > 0)
            {
                masterRow.SalesmenCategoryID = Convert.ToInt32(quotaListSalesmenNameDataTable.Rows[0]["SalesmenCategoryID"]);
                masterRow.SalesmenTypeID = Convert.ToInt32(quotaListSalesmenNameDataTable.Rows[0]["SalesmenTypeID"]);
                masterRow.SalesmenAddressAreaID = Convert.ToInt32(quotaListSalesmenNameDataTable.Rows[0]["AddressAreaID"]);
            }
            else
            {
                masterRow.SalesmenCategoryID = this.quotaMaster.SalesmenCategoryID;
                masterRow.SalesmenTypeID = this.quotaMaster.SalesmenTypeID;
                masterRow.SalesmenAddressAreaID = this.quotaMaster.SalesmenAddressAreaID;
            }

            masterRow.LocationID = this.quotaMaster.LocationID;
            masterRow.DepartmentID = this.quotaMaster.DepartmentID;
            masterRow.SectionID = this.quotaMaster.SectionID;
            masterRow.TeamID = this.quotaMaster.TeamID;
            masterRow.EmployeeID = this.quotaMaster.EmployeeID;
            masterRow.CustomerCategoryID = this.quotaMaster.CustomerCategoryID;
            masterRow.CustomerTypeID = this.quotaMaster.CustomerTypeID;
            masterRow.CustomerChannelID = this.quotaMaster.CustomerChannelID;

            double totalQuantity = 0;
            double totalAmount = 0;

            foreach (QuotaDetail quotaDetail in this.QuotaDetailList)
            {
                if (quotaDetail.QuantityQuota01 + quotaDetail.QuantityQuota02 + quotaDetail.QuantityQuota03 + quotaDetail.QuantityQuota04 + quotaDetail.QuantityQuota05 + quotaDetail.QuantityQuota06 +
                    quotaDetail.QuantityQuota07 + quotaDetail.QuantityQuota08 + quotaDetail.QuantityQuota09 + quotaDetail.QuantityQuota10 + quotaDetail.QuantityQuota11 + quotaDetail.QuantityQuota12 +
                    quotaDetail.AmountQuota01 + quotaDetail.AmountQuota02 + quotaDetail.AmountQuota03 + quotaDetail.AmountQuota04 + quotaDetail.AmountQuota05 + quotaDetail.AmountQuota06 +
                    quotaDetail.AmountQuota07 + quotaDetail.AmountQuota08 + quotaDetail.AmountQuota09 + quotaDetail.AmountQuota10 + quotaDetail.AmountQuota11 + quotaDetail.AmountQuota12 > 0)
                {
                    totalAmount = totalAmount + quotaDetail.AmountQuota01 + quotaDetail.AmountQuota02 + quotaDetail.AmountQuota03 + quotaDetail.AmountQuota04 + quotaDetail.AmountQuota05 + quotaDetail.AmountQuota06 + quotaDetail.AmountQuota07 + quotaDetail.AmountQuota08 + quotaDetail.AmountQuota09 + quotaDetail.AmountQuota10 + quotaDetail.AmountQuota11 + quotaDetail.AmountQuota12;
                    totalQuantity = totalQuantity + quotaDetail.QuantityQuota01 + quotaDetail.QuantityQuota02 + quotaDetail.QuantityQuota03 + quotaDetail.QuantityQuota04 + quotaDetail.QuantityQuota05 + quotaDetail.QuantityQuota06 + quotaDetail.QuantityQuota07 + quotaDetail.QuantityQuota08 + quotaDetail.QuantityQuota09 + quotaDetail.QuantityQuota10 + quotaDetail.QuantityQuota11 + quotaDetail.QuantityQuota12;
                }
            }
            masterRow.TotalQuantityQuota = totalQuantity;
            masterRow.TotalAmountQuota = totalAmount;

            masterRow.Description = this.quotaMaster.Description;
            masterRow.Other = this.quotaMaster.Other;

            masterRow.InActive = this.quotaMaster.InActive;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.quotaMaster.QuotaID <= 0) masterDataTable.AddQuotaMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            quotaID = masterRow.QuotaID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int quotaID)
        {
            int serialID = 0; int rowsAffected = 0; int rowsAccept = 0;


            #region <Save collection>

            serialID = 0;

            QuotaDTS.QuotaDetailDataTable detailDataTable = new QuotaDTS.QuotaDetailDataTable();

            foreach (QuotaDetail quotaDetail in this.QuotaDetailList)
            {
                if (quotaDetail.QuantityQuota01 + quotaDetail.QuantityQuota02 + quotaDetail.QuantityQuota03 + quotaDetail.QuantityQuota04 + quotaDetail.QuantityQuota05 + quotaDetail.QuantityQuota06 +
                    quotaDetail.QuantityQuota07 + quotaDetail.QuantityQuota08 + quotaDetail.QuantityQuota09 + quotaDetail.QuantityQuota10 + quotaDetail.QuantityQuota11 + quotaDetail.QuantityQuota12 +
                    quotaDetail.AmountQuota01 + quotaDetail.AmountQuota02 + quotaDetail.AmountQuota03 + quotaDetail.AmountQuota04 + quotaDetail.AmountQuota05 + quotaDetail.AmountQuota06 +
                    quotaDetail.AmountQuota07 + quotaDetail.AmountQuota08 + quotaDetail.AmountQuota09 + quotaDetail.AmountQuota10 + quotaDetail.AmountQuota11 + quotaDetail.AmountQuota12 > 0)
                {
                    rowsAccept++;

                    QuotaDTS.QuotaDetailRow detailRow = detailDataTable.NewQuotaDetailRow();

                    detailRow.QuotaID = quotaID;
                    detailRow.SerialID = ++serialID;

                    detailRow.CustomerCategoryID = this.quotaMaster.CustomerCategoryID;
                    detailRow.CustomerTypeID = this.quotaMaster.CustomerTypeID;
                    detailRow.CollectedTypeID = quotaDetail.CollectedTypeID;
                    detailRow.CommodityID = quotaDetail.CommodityID;
                    detailRow.CustomerChannelID = this.quotaMaster.CustomerChannelID;

                    detailRow.QuantityQuota12 = quotaDetail.QuantityQuota12;
                    detailRow.QuantityQuota11 = quotaDetail.QuantityQuota11;
                    detailRow.QuantityQuota10 = quotaDetail.QuantityQuota10;
                    detailRow.QuantityQuota09 = quotaDetail.QuantityQuota09;
                    detailRow.QuantityQuota08 = quotaDetail.QuantityQuota08;
                    detailRow.QuantityQuota07 = quotaDetail.QuantityQuota07;
                    detailRow.QuantityQuota06 = quotaDetail.QuantityQuota06;
                    detailRow.QuantityQuota05 = quotaDetail.QuantityQuota05;
                    detailRow.QuantityQuota04 = quotaDetail.QuantityQuota04;
                    detailRow.QuantityQuota03 = quotaDetail.QuantityQuota03;
                    detailRow.QuantityQuota02 = quotaDetail.QuantityQuota02;
                    detailRow.QuantityQuota01 = quotaDetail.QuantityQuota01;

                    detailRow.AmountQuota12 = quotaDetail.AmountQuota12;
                    detailRow.AmountQuota11 = quotaDetail.AmountQuota11;
                    detailRow.AmountQuota10 = quotaDetail.AmountQuota10;
                    detailRow.AmountQuota09 = quotaDetail.AmountQuota09;
                    detailRow.AmountQuota08 = quotaDetail.AmountQuota08;
                    detailRow.AmountQuota07 = quotaDetail.AmountQuota07;
                    detailRow.AmountQuota06 = quotaDetail.AmountQuota06;
                    detailRow.AmountQuota05 = quotaDetail.AmountQuota05;
                    detailRow.AmountQuota04 = quotaDetail.AmountQuota04;
                    detailRow.AmountQuota03 = quotaDetail.AmountQuota03;
                    detailRow.AmountQuota02 = quotaDetail.AmountQuota02;
                    detailRow.AmountQuota01 = quotaDetail.AmountQuota01;

                    detailDataTable.AddQuotaDetailRow(detailRow);
                }
            }

            rowsAffected = this.QuotaDetailTableAdapter.Update(detailDataTable);
            if (rowsAffected != rowsAccept) throw new System.ArgumentException("Insufficient save", "Save detail");


            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int quotaID)
        {
            int rowsDeleted = this.QuotaDetailTableAdapter.Delete(quotaID);

            return true;
        }


        private bool SaveConflict()
        {
            //return GlobalDataObjectAction.SaveConflict("QuotaSaveConflict", this.QuotaID);
            return false;
        }

        #endregion


        #region Import Excel



        public bool ImportExcel(string fileName)
        {
            try
            {
                DateTime dateTimeValue; string salesmenNameDescription = "";
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                TimeSpan timeout = TimeSpan.FromMinutes(90);
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, timeout))
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict");

                    DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.Quota, fileName);
                    if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                    {
                        string importSalesmenID = "";
                        QuotaBLL importQuotaBLL = new QuotaBLL();


                        importQuotaBLL.quotaDetailList.Clear();

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (importSalesmenID != excelDataRow["SalesmenID"].ToString().Trim())
                            {//Start a new importQuotaBLL
                                importSalesmenID = excelDataRow["SalesmenID"].ToString().Trim();

                                //Save current importQuotaBLL if existing
                                if (!exceptionTable.IsDirty && importQuotaBLL.quotaMaster.SalesmenID > 0)
                                    if (importQuotaBLL.IsDirty && !importQuotaBLL.Save())
                                        exceptionTable.AddException(new string[] { "Insufficient save", salesmenNameDescription });

                                //Start a new importQuotaBLL
                                importQuotaBLL.New();
                                exceptionTable.ClearDirty();

                                ERmgrUIP.SPGetSalesmenNameRow salesmenNameRow = CommonDatabaseMethod.GetSalesmenName(0, excelDataRow["SalesmenID"].ToString().Trim());
                                if (salesmenNameRow != null)
                                {
                                    importQuotaBLL.QuotaMaster.SalesmenID = salesmenNameRow.SalesmenID; salesmenNameDescription = excelDataRow["SalesmenID"].ToString().Trim();
                                }
                                else
                                {//Handle exception when MarketingProgram can not be found
                                    exceptionTable.AddException(new string[] { "Salesmen", excelDataRow["SalesmenID"].ToString() });
                                }


                                ERmgrUIP.SPGetCustomerChannelRow customerChannelRow = CommonDatabaseMethod.GetCustomerChannel(0, excelDataRow["CustomerChannelID"].ToString().Trim(), false);
                                if (customerChannelRow != null)
                                    importQuotaBLL.QuotaMaster.CustomerChannelID = customerChannelRow.CustomerChannelID;
                                else
                                {//Handle exception when MarketingPaymentType can not be found
                                    exceptionTable.AddException(new string[] { "Customer Channel", excelDataRow["CustomerChannelID"].ToString() });
                                }


                                ERmgrUIP.SPGetCustomerCategoryRow customerCategoryRow = CommonDatabaseMethod.GetCustomerCategory(0, excelDataRow["CustomerCategoryID"].ToString().Trim(), false);
                                if (customerCategoryRow != null)
                                    importQuotaBLL.QuotaMaster.CustomerCategoryID = customerCategoryRow.CustomerCategoryID;
                                else
                                {//Handle exception when MarketingPaymentType can not be found
                                    exceptionTable.AddException(new string[] { "Customer Class", excelDataRow["CustomerCategoryID"].ToString() });
                                }


                                ERmgrUIP.SPGetCustomerTypeRow customerTypeRow = CommonDatabaseMethod.GetCustomerType(0, excelDataRow["CustomerTypeID"].ToString().Trim(), false);
                                if (customerTypeRow != null)
                                    importQuotaBLL.QuotaMaster.CustomerTypeID = customerTypeRow.CustomerTypeID;
                                else
                                {//Handle exception when MarketingPaymentType can not be found
                                    exceptionTable.AddException(new string[] { "Customer Type", excelDataRow["CustomerTypeID"].ToString() });
                                }


                                if (DateTime.TryParse(excelDataRow["QuotaDate"].ToString(), out dateTimeValue)) importQuotaBLL.QuotaMaster.QuotaDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["QuotaDate"].ToString() });
                                if (DateTime.TryParse(excelDataRow["EntryDate"].ToString(), out dateTimeValue)) importQuotaBLL.QuotaMaster.EntryDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["EntryDate"].ToString() });

                            }

                            #region Import Detail Collection

                            ERmgrUIP.SPGetItemCommodityRow itemCommodityRow = CommonDatabaseMethod.GetItemCommodity(0, excelDataRow["CommodityID"].ToString().Trim());
                            if (itemCommodityRow != null)
                            {
                                for (int i = 1; i <= 12; i++)
                                {
                                    excelDataRow["QuantityQuota" + i.ToString("00")] = excelDataRow["QuantityQuota" + i.ToString("00")].ToString().Trim() == "" ? "0" : excelDataRow["QuantityQuota" + i.ToString("00")].ToString().Trim();
                                    excelDataRow["AmountQuota" + i.ToString("00")] = excelDataRow["AmountQuota" + i.ToString("00")].ToString().Trim() == "" ? "0" : excelDataRow["AmountQuota" + i.ToString("00")].ToString().Trim();
                                }

                                importQuotaBLL.quotaDetailList.Add(new QuotaDetail(importQuotaBLL.QuotaMaster.CustomerCategoryID, importQuotaBLL.QuotaMaster.CustomerTypeID, 0, itemCommodityRow.CommodityID, importQuotaBLL.QuotaMaster.CustomerChannelID, Convert.ToDouble(excelDataRow["QuantityQuota12"]), Convert.ToDouble(excelDataRow["QuantityQuota11"]), Convert.ToDouble(excelDataRow["QuantityQuota10"]), Convert.ToDouble(excelDataRow["QuantityQuota09"]), Convert.ToDouble(excelDataRow["QuantityQuota08"]), Convert.ToDouble(excelDataRow["QuantityQuota07"]), Convert.ToDouble(excelDataRow["QuantityQuota06"]), Convert.ToDouble(excelDataRow["QuantityQuota05"]), Convert.ToDouble(excelDataRow["QuantityQuota04"]), Convert.ToDouble(excelDataRow["QuantityQuota03"]), Convert.ToDouble(excelDataRow["QuantityQuota02"]), Convert.ToDouble(excelDataRow["QuantityQuota01"]), Convert.ToDouble(excelDataRow["AmountQuota12"]), Convert.ToDouble(excelDataRow["AmountQuota11"]), Convert.ToDouble(excelDataRow["AmountQuota10"]), Convert.ToDouble(excelDataRow["AmountQuota09"]), Convert.ToDouble(excelDataRow["AmountQuota08"]), Convert.ToDouble(excelDataRow["AmountQuota07"]), Convert.ToDouble(excelDataRow["AmountQuota06"]), Convert.ToDouble(excelDataRow["AmountQuota05"]), Convert.ToDouble(excelDataRow["AmountQuota04"]), Convert.ToDouble(excelDataRow["AmountQuota03"]), Convert.ToDouble(excelDataRow["AmountQuota02"]), Convert.ToDouble(excelDataRow["AmountQuota01"]), "", "", "", "", true));
                                //importQuotaBLL.quotaDetailList.Add(new QuotaDetail(importQuotaBLL.QuotaMaster.CustomerCategoryID, importQuotaBLL.QuotaMaster.CustomerTypeID, 0, itemCommodityRow.CommodityID, importQuotaBLL.QuotaMaster.CustomerChannelID, Convert.ToDouble(excelDataRow["QuantityQuota12"]), Convert.ToDouble(excelDataRow["QuantityQuota11"]), Convert.ToDouble(excelDataRow["QuantityQuota10"]), Convert.ToDouble(excelDataRow["QuantityQuota09"]), Convert.ToDouble(excelDataRow["QuantityQuota08"]), Convert.ToDouble(excelDataRow["QuantityQuota07"]), Convert.ToDouble(excelDataRow["QuantityQuota06"]), Convert.ToDouble(excelDataRow["QuantityQuota05"]), Convert.ToDouble(excelDataRow["QuantityQuota04"]), Convert.ToDouble(excelDataRow["QuantityQuota03"]), Convert.ToDouble(excelDataRow["QuantityQuota02"]), Convert.ToDouble(excelDataRow["QuantityQuota01"]), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "", "", "", "", true));                               

                            }

                            else
                            {//Handle exception when CustomerName can not be found
                                exceptionTable.AddException(new string[] { "Commodity", excelDataRow["CommodityID"].ToString() });
                            }

                            #endregion Import Detail Collection

                        }

                        //Save the last importMarketingIncentiveBLL after loop for all excelDataRow
                        if (!exceptionTable.IsDirty)
                            if (importQuotaBLL.IsDirty && !importQuotaBLL.Save())
                                exceptionTable.AddException(new string[] { "Insufficient save", salesmenNameDescription });
                        //--------------OK


                    }
                    if (exceptionTable.Table.Rows.Count <= 0)
                        transactionScope.Complete();
                    else
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);

                }
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }

        }

        #endregion Import Excel


        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;




            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      LEFT(CONVERT(char(10), QuotaDate, 111), 7) AS BeginingMonth, QuotaMaster.QuotaID, QuotaMaster.Reference, QuotaMaster.QuotaDate, QuotaMaster.EntryDate, QuotaMaster.TotalQuantityQuota, QuotaMaster.TotalAmountQuota, QuotaMaster.InActive, QuotaMaster.CustomerCategoryID, QuotaMaster.CustomerTypeID, QuotaMaster.CustomerChannelID, ";
            queryString = queryString + "                   QuotaMaster.SalesmenID, ListSalesmenName.DescriptionOfficial + ' ' + ListSalesmenName.DescriptionLastName AS SalesmenName, ListSalesmenCategory.Description AS SalesmenCategoryName, ListAddressArea.Description AS SalesmenAddressAreaName ";
            queryString = queryString + "       FROM        QuotaMaster INNER JOIN ";
            queryString = queryString + "                   ListSalesmenName ON QuotaMaster.SalesmenID = ListSalesmenName.SalesmenID INNER JOIN ";
            queryString = queryString + "                   ListSalesmenCategory ON QuotaMaster.SalesmenCategoryID =ListSalesmenCategory.SalesmenCategoryID INNER JOIN ";
            queryString = queryString + "                   ListAddressArea ON QuotaMaster.SalesmenAddressAreaID = ListAddressArea.AddressAreaID ";
            queryString = queryString + "       WHERE       QuotaDate >= @LowerFillterDate AND QuotaDate <= @UpperFillterDate " + "\r\n";
            SQLDatabase.CreateStoredProcedure("QuotaMasterListing", queryString);





            queryString = "     @QuotaID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      QuotaID, Reference, QuotaDate, EntryDate, SalesmenID, Description, Other, LocationID, DepartmentID, SectionID, TeamID, EmployeeID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantityQuota, TotalAmountQuota, InActive, UserID, UserOrganizationID, CustomerCategoryID, CustomerTypeID, CustomerChannelID " + "\r\n";
            queryString = queryString + "       FROM        QuotaMaster " + "\r\n";
            queryString = queryString + "       WHERE       QuotaID = @QuotaID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("QuotaMasterSelect", queryString);


            queryString = "    @Reference nvarchar(60), @QuotaDate datetime, @EntryDate datetime, @SalesmenID int, @Description nvarchar(100), @Other nvarchar(100), @LocationID int, @DepartmentID int, @SectionID int, @TeamID int, @EmployeeID int, @SalesmenCategoryID int, @SalesmenTypeID int, @SalesmenAddressAreaID int, @TotalQuantityQuota float, @TotalAmountQuota float, @InActive bit, @UserID int, @UserOrganizationID int, @CustomerCategoryID int, @CustomerTypeID int, @CustomerChannelID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO QuotaMaster (Reference, QuotaDate, EntryDate, SalesmenID, Description, Other, LocationID, DepartmentID, SectionID, TeamID, EmployeeID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantityQuota, TotalAmountQuota, InActive, UserID, UserOrganizationID, CustomerCategoryID, CustomerTypeID, CustomerChannelID) VALUES (@Reference, @QuotaDate, @EntryDate, @SalesmenID, @Description, @Other, @LocationID, @DepartmentID, @SectionID, @TeamID, @EmployeeID, @SalesmenCategoryID, @SalesmenTypeID, @SalesmenAddressAreaID, @TotalQuantityQuota, @TotalAmountQuota, @InActive, @UserID, @UserOrganizationID, @CustomerCategoryID, @CustomerTypeID, @CustomerChannelID) " + "\r\n";
            queryString = queryString + "       SELECT      QuotaID, Reference, QuotaDate, EntryDate, SalesmenID, Description, Other, LocationID, DepartmentID, SectionID, TeamID, EmployeeID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantityQuota, TotalAmountQuota, InActive, UserID, UserOrganizationID, CustomerCategoryID, CustomerTypeID, CustomerChannelID FROM QuotaMaster WHERE QuotaID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("QuotaMasterInsert", queryString);


            queryString = "     @QuotaID int, @Reference nvarchar(60), @QuotaDate datetime, @EntryDate datetime, @SalesmenID int, @Description nvarchar(100), @Other nvarchar(100), @LocationID int, @DepartmentID int, @SectionID int, @TeamID int, @EmployeeID int, @SalesmenCategoryID int, @SalesmenTypeID int, @SalesmenAddressAreaID int, @TotalQuantityQuota float, @TotalAmountQuota float, @InActive bit, @UserID int, @UserOrganizationID int, @CustomerCategoryID int, @CustomerTypeID int, @CustomerChannelID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      QuotaMaster SET Reference = @Reference, QuotaDate = @QuotaDate, EntryDate = @EntryDate, SalesmenID = @SalesmenID, Description = @Description, Other = @Other, LocationID = @LocationID, DepartmentID = @DepartmentID, SectionID = @SectionID, TeamID = @TeamID, EmployeeID = @EmployeeID, SalesmenCategoryID = @SalesmenCategoryID, SalesmenTypeID = @SalesmenTypeID, SalesmenAddressAreaID = @SalesmenAddressAreaID, TotalQuantityQuota = @TotalQuantityQuota, TotalAmountQuota = @TotalAmountQuota, InActive = @InActive, UserID = @UserID, UserOrganizationID = @UserOrganizationID, CustomerCategoryID = @CustomerCategoryID, CustomerTypeID = @CustomerTypeID, CustomerChannelID = @CustomerChannelID WHERE QuotaID = @QuotaID " + "\r\n";
            queryString = queryString + "       SELECT      QuotaID, Reference, QuotaDate, EntryDate, SalesmenID, Description, Other, LocationID, DepartmentID, SectionID, TeamID, EmployeeID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantityQuota, TotalAmountQuota, InActive, UserID, UserOrganizationID, CustomerCategoryID, CustomerTypeID, CustomerChannelID FROM QuotaMaster WHERE QuotaID = @QuotaID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("QuotaMasterUpdate", queryString);


            queryString = " @QuotaID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM QuotaMaster WHERE QuotaID = @QuotaID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("QuotaMasterDelete", queryString);






            queryString = " @QuotaID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      QuotaID, SerialID, CustomerCategoryID, CustomerTypeID, CollectedTypeID, CommodityID, CustomerChannelID, QuantityQuota12, QuantityQuota11, QuantityQuota10, QuantityQuota09, QuantityQuota08, QuantityQuota07, QuantityQuota06, QuantityQuota05, QuantityQuota04, QuantityQuota03, QuantityQuota02, QuantityQuota01, AmountQuota12, AmountQuota11, AmountQuota10, AmountQuota09, AmountQuota08, AmountQuota07, AmountQuota06, AmountQuota05, AmountQuota04, AmountQuota03, AmountQuota02, AmountQuota01 " + "\r\n";
            queryString = queryString + "       FROM        QuotaDetail " + "\r\n";
            queryString = queryString + "       WHERE       QuotaID = @QuotaID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("QuotaDetailSelect", queryString);


            queryString = " @QuotaID int, @SerialID int, @CustomerCategoryID int, @CustomerTypeID int, @CollectedTypeID int, @CommodityID int, @CustomerChannelID int, @QuantityQuota12 float, @QuantityQuota11 float, @QuantityQuota10 float, @QuantityQuota09 float, @QuantityQuota08 float, @QuantityQuota07 float, @QuantityQuota06 float, @QuantityQuota05 float, @QuantityQuota04 float, @QuantityQuota03 float, @QuantityQuota02 float, @QuantityQuota01 float, @AmountQuota12 float, @AmountQuota11 float, @AmountQuota10 float, @AmountQuota09 float, @AmountQuota08 float, @AmountQuota07 float, @AmountQuota06 float, @AmountQuota05 float, @AmountQuota04 float, @AmountQuota03 float, @AmountQuota02 float, @AmountQuota01 float " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO QuotaDetail (QuotaID, SerialID, CustomerCategoryID, CustomerTypeID, CollectedTypeID, CommodityID, CustomerChannelID, QuantityQuota12, QuantityQuota11, QuantityQuota10, QuantityQuota09, QuantityQuota08, QuantityQuota07, QuantityQuota06, QuantityQuota05, QuantityQuota04, QuantityQuota03, QuantityQuota02, QuantityQuota01, AmountQuota12, AmountQuota11, AmountQuota10, AmountQuota09, AmountQuota08, AmountQuota07, AmountQuota06, AmountQuota05, AmountQuota04, AmountQuota03, AmountQuota02, AmountQuota01) VALUES (@QuotaID, @SerialID, @CustomerCategoryID, @CustomerTypeID, @CollectedTypeID, @CommodityID, @CustomerChannelID, @QuantityQuota12, @QuantityQuota11, @QuantityQuota10, @QuantityQuota09, @QuantityQuota08, @QuantityQuota07, @QuantityQuota06, @QuantityQuota05, @QuantityQuota04, @QuantityQuota03, @QuantityQuota02, @QuantityQuota01, @AmountQuota12, @AmountQuota11, @AmountQuota10, @AmountQuota09, @AmountQuota08, @AmountQuota07, @AmountQuota06, @AmountQuota05, @AmountQuota04, @AmountQuota03, @AmountQuota02, @AmountQuota01) " + "\r\n";
            queryString = queryString + "       SELECT      QuotaID, SerialID, CustomerCategoryID, CustomerTypeID, CollectedTypeID, CommodityID, CustomerChannelID, QuantityQuota12, QuantityQuota11, QuantityQuota10, QuantityQuota09, QuantityQuota08, QuantityQuota07, QuantityQuota06, QuantityQuota05, QuantityQuota04, QuantityQuota03, QuantityQuota02, QuantityQuota01, AmountQuota12, AmountQuota11, AmountQuota10, AmountQuota09, AmountQuota08, AmountQuota07, AmountQuota06, AmountQuota05, AmountQuota04, AmountQuota03, AmountQuota02, AmountQuota01 FROM QuotaDetail WHERE (QuotaID = @QuotaID) AND (SerialID = @SerialID) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("QuotaDetailInsert", queryString);



            queryString = " @QuotaID int, @SerialID int, @CustomerCategoryID int, @CustomerTypeID int, @CollectedTypeID int, @CommodityID int, @CustomerChannelID int, @QuantityQuota12 float, @QuantityQuota11 float, @QuantityQuota10 float, @QuantityQuota09 float, @QuantityQuota08 float, @QuantityQuota07 float, @QuantityQuota06 float, @QuantityQuota05 float, @QuantityQuota04 float, @QuantityQuota03 float, @QuantityQuota02 float, @QuantityQuota01 float, @AmountQuota12 float, @AmountQuota11 float, @AmountQuota10 float, @AmountQuota09 float, @AmountQuota08 float, @AmountQuota07 float, @AmountQuota06 float, @AmountQuota05 float, @AmountQuota04 float, @AmountQuota03 float, @AmountQuota02 float, @AmountQuota01 float " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      QuotaDetail SET CustomerCategoryID = @CustomerCategoryID, CustomerTypeID = @CustomerTypeID, CollectedTypeID = @CollectedTypeID, CommodityID = @CommodityID, CustomerChannelID = @CustomerChannelID, QuantityQuota12 = @QuantityQuota12, QuantityQuota11 = @QuantityQuota11, QuantityQuota10 = @QuantityQuota10, QuantityQuota09 = @QuantityQuota09, QuantityQuota08 = @QuantityQuota08, QuantityQuota07 = @QuantityQuota07, QuantityQuota06 = @QuantityQuota06, QuantityQuota05 = @QuantityQuota05, QuantityQuota04 = @QuantityQuota04, QuantityQuota03 = @QuantityQuota03, QuantityQuota02 = @QuantityQuota02, QuantityQuota01 = @QuantityQuota01, AmountQuota12 = @AmountQuota12, AmountQuota11 = @AmountQuota11, AmountQuota10 = @AmountQuota10, AmountQuota09 = @AmountQuota09, AmountQuota08 = @AmountQuota08, AmountQuota07 = @AmountQuota07, AmountQuota06 = @AmountQuota06, AmountQuota05 = @AmountQuota05, AmountQuota04 = @AmountQuota04, AmountQuota03 = @AmountQuota03, AmountQuota02 = @AmountQuota02, AmountQuota01 = @AmountQuota01 WHERE QuotaID = @QuotaID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      QuotaID, SerialID, CustomerCategoryID, CustomerTypeID, CollectedTypeID, CommodityID, CustomerChannelID, QuantityQuota12, QuantityQuota11, QuantityQuota10, QuantityQuota09, QuantityQuota08, QuantityQuota07, QuantityQuota06, QuantityQuota05, QuantityQuota04, QuantityQuota03, QuantityQuota02, QuantityQuota01, AmountQuota12, AmountQuota11, AmountQuota10, AmountQuota09, AmountQuota08, AmountQuota07, AmountQuota06, AmountQuota05, AmountQuota04, AmountQuota03, AmountQuota02, AmountQuota01 FROM QuotaDetail WHERE QuotaID = @QuotaID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("QuotaDetailUpdate", queryString);



            queryString = " @QuotaID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM QuotaDetail WHERE QuotaID = @QuotaID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("QuotaDetailDelete", queryString);



            queryString = " @QuotaID Int, @CustomerCategoryID int, @CustomerTypeID int, @CustomerChannelID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            queryString = queryString + "       SELECT      IsCommodity, CommodityID, REPLICATE('     ', HierarchyLevel -1) + ItemCategoryName AS ItemCategoryName, ItemCommodityName, AncestorID, HierarchyOrder, HierarchyLevel, BrandName, " + "\r\n";
            queryString = queryString + "                   CustomerCategoryID, CustomerTypeID, CustomerChannelID, CollectedTypeID, Unit, SerialID, ";
            queryString = queryString + "                   QuantityQuota01, QuantityQuota02, QuantityQuota03, QuantityQuota04, QuantityQuota05, QuantityQuota06,";
            queryString = queryString + "                   QuantityQuota07, QuantityQuota08, QuantityQuota09, QuantityQuota10, QuantityQuota11, QuantityQuota12,";
            queryString = queryString + "                   AmountQuota01, AmountQuota02,AmountQuota03, AmountQuota04, AmountQuota05, AmountQuota06,";
            queryString = queryString + "                   AmountQuota07, AmountQuota08, AmountQuota09, AmountQuota10, AmountQuota11, AmountQuota12 ";
            queryString = queryString + "      FROM (";

            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCommodity, 100000 * ItemCategoryID AS CommodityID, Description AS ItemCategoryName, '' AS ItemCommodityName, 100000 * AncestorID AS AncestorID, HierarchyOrder, HierarchyLevel, '' AS BrandName, " + "\r\n";
            queryString = queryString + "                   0 AS CustomerCategoryID, 0 AS CustomerTypeID, 0 AS CustomerChannelID, 0 AS CollectedTypeID, '' AS Unit, 0 AS SerialID, ";
            queryString = queryString + "                   0 AS QuantityQuota01, 0 AS QuantityQuota02, 0 AS QuantityQuota03, 0 AS QuantityQuota04, 0 AS QuantityQuota05, 0 AS QuantityQuota06,";
            queryString = queryString + "                   0 AS QuantityQuota07, 0 AS QuantityQuota08, 0 AS QuantityQuota09, 0 AS QuantityQuota10, 0 AS QuantityQuota11, 0 AS QuantityQuota12,";
            queryString = queryString + "                   0 AS AmountQuota01, 0 AS AmountQuota02,0 AS AmountQuota03, 0 AS AmountQuota04, 0 AS AmountQuota05, 0 AS AmountQuota06,";
            queryString = queryString + "                   0 AS AmountQuota07, 0 AS AmountQuota08, 0 AS AmountQuota09, 0 AS AmountQuota10, 0 AS AmountQuota11, 0 AS AmountQuota12 ";
            queryString = queryString + "       FROM        ListItemCategory " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";

            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCommodity, ListItemCommodity.CommodityID AS CommodityID, '' AS ItemCategoryName, ListItemCommodity.DescriptionOfficial AS ItemCommodityName, 100000 * ListItemCommodity.ItemCategoryID AS AncestorID, ListItemCategory.HierarchyOrder, ListItemCategory.HierarchyLevel + 1 AS HierarchyLevel, ListItemBrand.Description AS BrandName, " + "\r\n";
            queryString = queryString + "                   ISNULL(QuotaDetail.CustomerCategoryID, 0) AS CustomerCategoryID, ISNULL(QuotaDetail.CustomerTypeID, 0) AS CustomerTypeID, ISNULL(QuotaDetail.CustomerChannelID, 0) AS CustomerChannelID, ISNULL(QuotaDetail.CollectedTypeID, 0) AS CollectedTypeID, ListItemCommodity.Unit, ISNULL(QuotaDetail.SerialID, 0), ";
            queryString = queryString + "                   ISNULL(QuotaDetail.QuantityQuota01,0) AS QuantityQuota01, ISNULL(QuotaDetail.QuantityQuota02, 0)  AS QuantityQuota02, ISNULL(QuotaDetail.QuantityQuota03, 0) AS QuantityQuota03, ISNULL(QuotaDetail.QuantityQuota04, 0) AS QuantityQuota04, ISNULL(QuotaDetail.QuantityQuota05, 0) AS QuantityQuota05, ISNULL(QuotaDetail.QuantityQuota06, 0)  AS QuantityQuota06,";
            queryString = queryString + "                   ISNULL(QuotaDetail.QuantityQuota07, 0) AS QuantityQuota07, ISNULL(QuotaDetail.QuantityQuota08, 0) AS QuantityQuota08, ISNULL(QuotaDetail.QuantityQuota09, 0) AS QuantityQuota09, ISNULL(QuotaDetail.QuantityQuota10, 0)  AS QuantityQuota10, ISNULL(QuotaDetail.QuantityQuota11, 0) AS QuantityQuota11, ISNULL(QuotaDetail.QuantityQuota12, 0) AS QuantityQuota12,";
            queryString = queryString + "                   ISNULL(QuotaDetail.AmountQuota01, 0) AS AmountQuota01, ISNULL(QuotaDetail.AmountQuota02, 0) AS AmountQuota02, ISNULL(QuotaDetail.AmountQuota03, 0) AS AmountQuota03, ISNULL(QuotaDetail.AmountQuota04, 0) AS AmountQuota04, ISNULL(QuotaDetail.AmountQuota05, 0) AS AmountQuota05, ISNULL(QuotaDetail.AmountQuota06, 0) AS AmountQuota06,";
            queryString = queryString + "                   ISNULL(QuotaDetail.AmountQuota07, 0) AS AmountQuota07, ISNULL(QuotaDetail.AmountQuota08, 0) AS AmountQuota08, ISNULL(QuotaDetail.AmountQuota09, 0) AS AmountQuota09, ISNULL(QuotaDetail.AmountQuota10, 0) AS AmountQuota10, ISNULL(QuotaDetail.AmountQuota11, 0) AS AmountQuota11, ISNULL(QuotaDetail.AmountQuota12,0) AS AmountQuota12 ";
            queryString = queryString + "       FROM        (ListItemCommodity LEFT JOIN  " + "\r\n";
            queryString = queryString + "                   (SELECT * FROM QuotaDetail WHERE (QuotaID = @QuotaID OR QuotaID <= 0) AND CustomerCategoryID = @CustomerCategoryID AND CustomerTypeID = @CustomerTypeID AND CustomerChannelID = @CustomerChannelID) AS QuotaDetail ON QuotaDetail.CommodityID = ListItemCommodity.CommodityID)  LEFT JOIN " + "\r\n";
            queryString = queryString + "                   ListItemBrand ON ListItemCommodity.ItemBrandID = ListItemBrand.ItemBrandID INNER JOIN  " + "\r\n";
            queryString = queryString + "                   ListItemCategory ON ListItemCommodity.ItemCategoryID = ListItemCategory.ItemCategoryID " + "\r\n";
            queryString = queryString + "        ) QuotaDetail " + "\r\n";
            queryString = queryString + "        ORDER BY HierarchyOrder, HierarchyLevel " + "\r\n";

            SQLDatabase.CreateStoredProcedure("QuotaGetDetail", queryString);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];
            //queryArray[0] = " SELECT TOP 1 QuotaID FROM QuotaMaster WHERE QuotaID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("SPQuotaEditable", queryArray);
        }
    }
}
